﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using XizheC;

namespace XizheC
{
    public class CREPLACE_MATERIEL
    {
        basec bc = new basec();
        #region natrue
        private string _sql;
        public string sql
        {
            set { _sql = value; }
            get { return _sql; }

        }
        private string _sqlo;
        public string sqlo
        {
            set { _sqlo = value; }
            get { return _sqlo; }

        }
        private string _sqlt;
        public string sqlt
        {
            set { _sqlt = value; }
            get { return _sqlt; }

        }
        private string _sqlth;
        public string sqlth
        {
            set { _sqlth = value; }
            get { return _sqlth; }

        }
        private string _sqlf;
        public string sqlf
        {
            set { _sqlf = value; }
            get { return _sqlf; }

        }
        private string _ErrowInfo;
        public string ErrowInfo
        {

            set { _ErrowInfo = value; }
            get { return _ErrowInfo; }

        }
        #endregion


        #region setsql
        string setsql = @"
SELECT
A.RMKEY AS 索引,
A.RMID AS 替代编号,
B.WareID AS ID,
C.WNAME AS 品名,
C.CO_WAREID AS 料号,
C.CWAREID AS 客户料号,
C.SPEC AS 规格,
A.DET_WAREID AS 子ID,
A.SN AS 项次,
D.WNAME AS 子品名,
D.CO_WAREID AS 子料号,
D.CWAREID AS 子客户料号,
D.SPEC AS 子规格,
A.REMARK AS 备注,
A.UNIT_DOSAGE AS 组成用量,
A.ATTRITION_RATE AS 损耗率,
CAST(ROUND((A.ATTRITION_RATE/100),2) AS DECIMAL(18,2)) AS 损耗量,
CAST(ROUND((A.UNIT_DOSAGE+A.ATTRITION_RATE/100),2) AS DECIMAL(18,2)) AS 需求量,
D.MPA_TO_STOCK AS MPA_TO_STOCK,
D.STOCK_TO_BOM AS STOCK_TO_BOM,
D.BOM_UNIT AS BOM单位,
D.SKU AS 库存单位,
A.IFC_SUPPLY AS 客供否,
A.PICKING_STAGE AS 发料阶段,
D.BRAND AS 品牌,
A.BIT_NUMBER AS 位号,
CASE WHEN E.CNAME IS NULL THEN  F.SNAME 
ELSE E.CNAME 
END AS 客户名称,
(SELECT ENAME FROM EMPLOYEEINFO WHERE EMID=B.MAKERID ) AS 制单人
FROM REPLACE_MATERIEL_DET A 
LEFT JOIN REPLACE_MATERIEL_MST B  ON A.RMID=B.RMID
LEFT JOIN WAREINFO C ON B.WAREID=C.WAREID
LEFT JOIN WAREINFO D ON D.WAREID=A.DET_WAREID
LEFT JOIN CUSTOMERINFO_MST E ON C.CUID=E.CUID
LEFT JOIN SupplierInfo_MST F ON C.CUID =F.SUID 
";
        #endregion
        #region setsqlo
        string setsqlo = @"
INSERT INTO 
REPLACE_MATERIEL_DET
(
RMKEY,
RMID,
SN,
DET_WAREID,
UNIT_DOSAGE,
ATTRITION_RATE,
IFC_SUPPLY,
PICKING_STAGE,
REMARK,
YEAR,
MONTH,
DAY,
BIT_NUMBER
)  
VALUES 
(
@RMKEY,
@RMID,
@SN,
@DET_WAREID,
@UNIT_DOSAGE,
@ATTRITION_RATE,
@IFC_SUPPLY,
@PICKING_STAGE,
@REMARK,
@YEAR,
@MONTH,
@DAY,
@BIT_NUMBER
)";
        #endregion
        #region setsqlt
        string setsqlt = @"
INSERT INTO 
REPLACE_MATERIEL_MST
(
RMID,
WAREID,
DATE,
MAKERID,
YEAR,
MONTH
)  
VALUES 
(
@RMID,
@WAREID,
@DATE,
@MAKERID,
@YEAR,
@MONTH
)";
        #endregion
        #region setsqlth
        string setsqlth = @"
UPDATE REPLACE_MATERIEL_MST SET 
WAREID=@WAREID,
DATE=@DATE,
MAKERID=@MAKERID,
YEAR=@YEAR,
MONTH=@MONTH
";
        #endregion
        #region setsqlf
        string setsqlf = @"
SELECT
B.RMID AS 替代编号,
B.WareID AS ID,
C.WNAME AS 品名,
C.CO_WAREID AS 料号,
C.CWAREID AS 客户料号,
C.SPEC AS 规格,
(SELECT ENAME FROM EMPLOYEEINFO WHERE EMID=B.MAKERID ) AS 制单人,
B.Date AS 制单日期
FROM REPLACE_MATERIEL_MST B
LEFT JOIN WAREINFO C ON B.WAREID=C.WAREID
LEFT JOIN CUSTOMERINFO_MST E ON C.CUID=E.CUID
";
        #endregion


        public CREPLACE_MATERIEL()
        {

            sql = setsql;
            sqlo = setsqlo;
            sqlt = setsqlt;
            sqlth = setsqlth;
            sqlf = setsqlf;

        }
        public bool IFNOEXISTS_BOM(string WAREID)
        {
            bool b = false;
            if (!bc.exists("SELECT * FROM REPLACE_MATERIEL_MST WHERE WAREID='" + WAREID + "'"))
            {
                b = true;
                ErrowInfo = "此料号不存在BOM表！";

            }
            else if(!bc.exists("SELECT * FROM REPLACE_MATERIEL_MST WHERE WAREID='" + WAREID + "' AND ACTIVE='Y'"))
            {
                b = true;
                ErrowInfo = "此料号BOM表未生效！";

            }
            
            return b;
        }
     public bool  IFNOALLOW_DELETE_BOM(string RMID)
    {
        bool b = false;
        if (bc.exists("SELECT * FROM MRP WHERE RMID='" + RMID  + "'"))
        {
            b = true;
            ErrowInfo = "该BOM已经存在MRP中，不允许修改与删除！";
        }
        else if (bc.exists("SELECT * FROM WORKORDER_MST WHERE RMID='" + RMID + "'"))
        {
            b = true;
            ErrowInfo = "该BOM已经存在工单中，不允许修改与删除！";
        }
            
        return b;
    }
     public string  GETBOM_TO_STOCK(string WAREID)
     {
         string b = bc.getOnlyString("SELECT MPA_TO_STOCK/STOCK_TO_BOM FROM WAREINFO WHERE WAREID='"+WAREID +"' AND ACTIVE='Y'");
         return b;
     }
  
    }
}
